<!DOCTYPE html>
<html>
    <head>
        <title>H5 SQL</title>
        <meta charset="UTF-8">
    </head>

    <body>
        <h1>H5 SQL</h1>
        <p>This is a test page for H5 SQL 112.</p>
        <p id="result"></p>
        <input type="button" value="查询" onclick="result2()">
        <input type="button" value="清空" onclick="clear1()">
        <input type="button" value="Add" onclick="addData()">

        <!-- 
        openDatabase is not defined 问题请自行查询相关解决
    -->

        <script>
        /* 更新的数据库操作 */
        let db;
        var age = 0;
        const request = indexedDB.open("mydb", 1);

        // 处理数据库升级
        request.onupgradeneeded = function(event) {
            console.log("建表成功");

            const db = event.target.result;
            const objectStore = db.createObjectStore("people", { keyPath: "id", autoIncrement: true });
            objectStore.createIndex("name", "name", { unique: false });
            objectStore.createIndex("age", "age", { unique: false });
        }; 

        // 数据库打开成功时的回调
        request.onsuccess = function(event) {
            db = event.target.result;
            const transaction = db.transaction("people", "readwrite");
            transaction.oncomplete = function() {
                console.log("数据打开成功");
            };
        };

        // 数据库打开失败
        request.onerror = function(event) {
            console.log("数据添加失败");
        }

        function addData() {
            const transaction = db.transaction("people", "readwrite");
            const objectStore = transaction.objectStore("people");
            objectStore.add({ name: "John", age: 30 + age});
            objectStore.add({ name: "Mary", age: 25 + age });
            objectStore.add({ name: "Bob", age: 40 + age });
            age++;
            transaction.oncomplete = function() {
                console.log("数据添加成功");
            };
        }

        function result2() {
            document.getElementById("result").innerHTML = "";
            
            var tb = request.result.transaction("people", "readonly").objectStore("people");
            tb.openCursor().onsuccess = function(event) {
                var cursor = event.target.result;
                if (cursor) {
                    console.log(cursor.value.name + " - " + cursor.value.age);
                    
                    var msg = cursor.value.name + " - " + cursor.value.age
                    document.getElementById("result").innerHTML += msg + "<br>";

                    cursor.continue();
                } else {
                    console.log("数据查询完毕");
                }
            }
        }

        function clear1() {
            request.result.transaction("people", "readwrite").objectStore("people").clear();
            document.getElementById("result").innerHTML = "";
        }

        function clear2() {
            request.result.transaction("people", "readwrite").objectStore("people").clear();
            document.getElementById("result").innerHTML = "";
        }

        /* 旧数据库 一些新浏览器可能不支持 */
        function result() {
            console.log("H5 SQL test page");

            var db = openDatabase("mydb", "1.0", "Test DB", 200000);
            db.transaction(function(tx) {
                tx.executeSql("CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)");
                tx.executeSql("INSERT INTO people (name, age) VALUES (?,?)", ["John", 30]);
                tx.executeSql("INSERT INTO people (name, age) VALUES (?,?)", ["Mary", 25]);
                tx.executeSql("INSERT INTO people (name, age) VALUES (?,?)", ["Bob", 40]);
            });

            db.transaction(function(tx) {
                document.getElementById("result").innerHTML = "";

                tx.executeSql("SELECT * FROM people", [], function(tx, results) {
                    var len = results.rows.length, i;
                    for (i = 0; i < len; i++) {
                        //console.log(results.rows.item(i).name + " - " + results.rows.item(i).age);
                    }
                    var msg = results.rows.item(i).name + " - " + results.rows.item(i).age;
                    document.getElementById("result").innerHTML += msg + "<br>------------------<br>";
                });
            });
        }
        
    </script>

    </body>
</html>
